import xlrd
import pymysql
#打开数据所在的工作簿，以及选择存有数据的工作表
#book = xlrd.open_workbook(r'road.xlsx')
file_name = r'C:\Users\admin\Desktop\road.xls'
book = xlrd.open_workbook(file_name)
sheet = book.sheet_by_name("工作表1")
#建立一个MySQL连接
conn = pymysql.connect(
        host='localhost', 
        user='root', 
        passwd='root',  
        db='python',  
        port=3306,  
        charset='utf8'
        )
# 获得游标
cur = conn.cursor()
# 创建插入SQL语句
query = 'insert into student_tbl (GUID,DesignUnit,Project,DrawingName,BookName) values ( %d, %s, %s, %s, %s)'
# 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题行
GUID          = sheet.cell(1,0).value
DesignUnit    = sheet.cell(1,1).value
Project       = sheet.cell(1,2).value
DrawingName   = sheet.cell(1,3).value
BookName      = sheet.cell(1,4).value

values = (GUID,DesignUnit,Project,DrawingName,BookName)
print(values)

""" for r in range(1, sheet.nrows):
      print(r) """
""" for r in range(2, sheet.nrows):

      DesignUnit    = sheet.cell(r,2).value
      Project       = sheet.cell(r,3).value
      DrawingName   = sheet.cell(r,4).value
      BookName      = sheet.cell(r,5).value

      values = (pymysql.NULL,DesignUnit,Project,DrawingName,BookName)
      print(values) """
      # 执行sql语句
      #cur.execute(query, values)
cur.close()
conn.commit()
conn.close()
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print ("导入 " +columns + " 列 " + rows + " 行数据到MySQL数据库!")